package dao;

import java.sql.*;
import java.util.Objects;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.Employee;

public class EmployeeDAO {
    private Connection conn;
    
    public EmployeeDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public boolean insert(@NonNull Employee employee) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("INSERT INTO EMPLOYEES VALUES(?,?,?,?,?,?,?)");
        ps.setString(1, employee.getEmployeeName());
        ps.setInt(2, employee.getDepartmentId());
        ps.setString(3, employee.getSex());
        ps.setDate(4, employee.getBirthdate());
        ps.setString(5, employee.getTelephone());
        ps.setString(6, employee.getAddress());
        ps.setDate(7, employee.getEnrollmentDate());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public Employee selectById(int id) throws SQLException {
        Employee employee = null;
        if (id < 1) {
            return employee;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT A.*,B.DEPARTMENT_NAME"
            + " FROM EMPLOYEES AS A INNER JOIN DEPARTMENTS AS B ON B.DEPARTMENT_ID=A.DEPARTMENT_ID"
            + " AND EMPLOYEE_ID=?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String name = rs.getString("EMPLOYEE_NAME");
            int departmentId = rs.getInt("DEPARTMENT_ID");
            String sex = rs.getString("SEX");
            Date birthdate = rs.getDate("BIRTHDATE");
            String telephone = rs.getString("TELEPHONE");
            String address = rs.getString("ADDRESS");
            Date enrollmentDate = rs.getDate("ENROLLMENT_DATE");
            String departmentName = rs.getString("DEPARTMENT_NAME");
            employee = new Employee(id, name, departmentId, sex, birthdate, telephone, address, enrollmentDate, departmentName);
        }
        rs.close();
        ps.close();
        return employee;
    }

    public Vector<Employee> selectByKeyword(String keyword, int departmentId) throws SQLException {
        Vector<Employee> employees = new Vector<>();
        boolean state1 = Objects.nonNull(keyword) && keyword.length() > 0;
        boolean state2 = departmentId > 0;
        boolean state = state1 && state2;
        String sql = "SELECT A.*,B.DEPARTMENT_NAME FROM EMPLOYEES AS A INNER JOIN DEPARTMENTS AS B"
            + " ON B.DEPARTMENT_ID=A.DEPARTMENT_ID";
        String sql1 = " AND EMPLOYEE_NAME LIKE ?";
        String sql2 = " AND B.DEPARTMENT_ID=?";
        keyword = "%" + keyword + "%";
        PreparedStatement ps = conn.prepareStatement(sql);
        if (state) {
            sql += sql1 + sql2;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
            ps.setInt(2, departmentId);
        } else if (state1) {
            sql += sql1;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
        } else if (state2) {
            sql += sql2;
            ps = conn.prepareStatement(sql);
            ps.setInt(1, departmentId);
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("EMPLOYEE_ID");
            String name = rs.getString("EMPLOYEE_NAME");
            departmentId = rs.getInt("DEPARTMENT_ID");
            String sex = rs.getString("SEX");
            String telephone = rs.getString("TELEPHONE");
            String departmentName = rs.getString("DEPARTMENT_NAME");
            Employee employee = new Employee(id, name, departmentId, sex, telephone, departmentName);
            employees.addElement(employee);
        }
        rs.close();
        ps.close();
        return employees;
    }

    public int selectIdCountByDepartmentId(int departmentId) throws SQLException {
        int count = -1;
        if (departmentId < 1) {
            return count;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE DEPARTMENT_ID=?");
        ps.setInt(1, departmentId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        ps.close();
        return count;
    }

    public boolean update(int id, @NonNull Employee employee) throws SQLException {
        if (id < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("UPDATE EMPLOYEES SET EMPLOYEE_NAME=?,DEPARTMENT_ID=?,SEX=?,"
            + "BIRTHDATE=?,TELEPHONE=?,ADDRESS=?,ENROLLMENT_DATE=? WHERE EMPLOYEE_ID=?");
        ps.setString(1, employee.getEmployeeName());
        ps.setInt(2, employee.getDepartmentId());
        ps.setString(3, employee.getSex());
        ps.setDate(4, employee.getBirthdate());
        ps.setString(5, employee.getTelephone());
        ps.setString(6, employee.getAddress());
        ps.setDate(7, employee.getEnrollmentDate());
        ps.setInt(8, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public boolean delete(int id) throws SQLException {
        if (id < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID=?");
        ps.setInt(1, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
